package br.com.cs.dao.jdbc;

import br.com.cs.exception.DAOException;
import br.com.cs.dao.LancamentoDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import br.com.cs.model.Lancamento;

public class LancamentoJDBC extends BaseDAOJDBC implements LancamentoDAO{
    
    private static String SQL_INSERT_LANCAMENTO =
            "insert into LANCAMENTO (CODIGO, TIPO, DESCRICAO) values (?, ?, ?)";
            
            private static String SQL_UPDATE_LANCAMENTO =
            "update categorias set visitante nome = ?, telefone = ? where id = ?";
           
            private static String SQL_DELETE_LANCAMENTO = "delete from LANCAMENTO where id = ? ";
            
            private static LancamentoJDBC instance = new LancamentoJDBC();
            
            
      protected LancamentoJDBC(){
      }     
      
      public static LancamentoJDBC getInstance() {
          return instance;
      }

    @Override
    protected List getManyByCriteria(String sql) throws DAOException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List alista = new ArrayList();
        
        try {
            conn = getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            
            while (rs.next()) {
                
                
                Lancamento lancamento = new Lancamento();
                lancamento.setId(new Integer(rs.getInt("ID")));
                lancamento.setCodigo(new Integer(rs.getInt("CODIGO")));
                lancamento.setTipo(rs.getString("TIPO"));
                lancamento.setDescricao(rs.getString("DESCRICAO"));

                alista.add(lancamento);
                
            }
            
            return alista;
            
        } catch (Exception e) {
            throw new DAOException("[LancamentoJDBC - getManyByCriteria] : Handled Exception  ", e);
            
        } finally {
            closeResources(conn, st, rs);
        }
    }

    @Override
    public void delete(Lancamento lancamento) throws DAOException {
        Connection conn = null;
        PreparedStatement pst = null;
        
        try {
            conn = getConnection();
            pst = conn.prepareStatement(SQL_DELETE_LANCAMENTO);
            
            pst.setInt(1, lancamento.getId().intValue());
            
            pst.executeUpdate();
            
        } catch (Exception e) {
            throw new DAOException("[LancamentoJDBC - delete] : Handled Exception  ", e);
            
        } finally {
            closeResources(conn, pst);
        }
    }

    @Override
    public List getAll() throws DAOException {
        String sql = "select * from LANCAMENTO ";
        return this.getManyByCriteria(sql);
    }

    @Override
    public Lancamento getByPrimaryKey(Integer id) throws DAOException {
        String sql = "select * from LANCAMENTO where id = " + id;
        List lista = this.getManyByCriteria(sql);
        if (lista != null && lista.size() > 0)
            return (Lancamento) lista.get(0);
        else
            return null;
    }

    @Override
    public void save(Lancamento lancamento) throws br.com.cs.exception.DAOException {
        Connection conn = null;
        PreparedStatement pst = null;
        
        try {
            conn = getConnection();
            if (lancamento.getId() == null) {
                System.out.println("[LancamentoJDBC] novo lancmaento vai ser incluido...");
                pst = conn.prepareStatement(SQL_INSERT_LANCAMENTO);
            } else {
                System.out.println("[LancamentoJDBC] lancamento ja existente vai ser regravado...");
                pst = conn.prepareStatement(SQL_UPDATE_LANCAMENTO);
                pst.setInt (4, lancamento.getId().intValue());
            }
            
            pst.setInt(1, lancamento.getCodigo());
            pst.setString(2, lancamento.getTipo());
            pst.setString(3, lancamento.getDescricao());
            
            pst.execute();
            
        } catch (Exception e) {
            throw new DAOException("[LancamentoJDBC - save] : Handled Exception  ", e);
            
        } finally {
            closeResources(conn, pst);
        } 
    }

    @Override
    public List getByFilter(String filterName, String filterValue) throws DAOException {
        String sql = "select * from LANCAMENTO where "+filterName+" = " + "'"+filterValue+"'";
        List lista = this.getManyByCriteria(sql);
        if (lista != null && lista.size() > 0)
            return lista;
        else{
            return null;
        }
    }
    
}
